package dblayer;

import modellayer.Clothing;
import modellayer.Equipment;
import modellayer.GunReplicas;
import modellayer.Product;
import modellayer.Supplier;

import java.sql.*;
import java.util.ArrayList;

/**
 * 
 * @author Gruppe 3
 *
 */
public class DBProduct implements IFDBProduct {

	 private Connection con;
	 private DBSupplier dbSup = new DBSupplier();

	 /**
	  * 
	  */
     public DBProduct() 
     {
    	 con = DBConnection.getInstance().getDBcon();
     }    
     
     /**
 	 * A arraylist with all Product
 	 */
 	 public ArrayList<Product> getAllProducts(boolean retriveAssociation) {
 	 	return miscWhere("", retriveAssociation);
 	 }
 	 
 	 
 	 /**
      * Select a single Product
      */
     public Product selectSingleProduct(int productID, boolean retriveAssociation) throws SQLException {
    	 String wClause = "productID = "+productID;
         return singleWhere(wClause, retriveAssociation);
     }
     
     /**
      * Insert a Product to the DB
      */
     public int insertProduct(Product p) throws SQLException 
     {
    	 //DBConnection.startTransaction();
    	 PreparedStatement pstmt = null;
    	 int controlInt = -1;
    	 String insert = "insert into Product(name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, "
    	 		+ "minStock, inStock, supplierID, productType)"
                + "values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    	 //System.out.println(insert);
    	 try {
    		 pstmt = con.prepareStatement(insert);
    		 pstmt.setString(1, p.getName());
    		 pstmt.setInt(2, p.getPurchasePrice());
             pstmt.setInt(3, p.getSalesPrice());
             pstmt.setInt(4, p.getRentPrice());
             pstmt.setString(5, p.getCountryOfOrigin());
             pstmt.setInt(6, p.getMinStock());
             pstmt.setInt(7, p.getInStock());
             pstmt.setInt(8, p.getSupplierID());
             pstmt.setString(9, p.getProductType());
             controlInt = pstmt.executeUpdate();
             
             //get the latest keys value:
             PreparedStatement stmtKeys = con.prepareStatement(insert, 
                     Statement.RETURN_GENERATED_KEYS);
             ResultSet keys = stmtKeys.getGeneratedKeys();
             keys.next();  
             int key = -1;
             key = keys.getInt(1);  
             keys.close();
             
             if (p instanceof GunReplicas) {
            	 PreparedStatement pstmtG = null;
            	 String insertG = "insert into GunReplicas(productID, fabric, calibre)"
                         + "values (?, ?, ?)";
            	 GunReplicas g = (GunReplicas) p;
            	 pstmtG = con.prepareStatement(insertG);
            	 pstmtG.setInt(1, key);
        		 pstmtG.setString(2, g.getFabric());
        		 pstmtG.setString(3, g.getCalibre());
        		 pstmtG.executeUpdate();
             }
             if (p instanceof Clothing) {
            	 PreparedStatement pstmtC = null;
            	 String insertC = "insert into Clothing(productID, size, color)"
                         + "values (?, ?, ?)";
            	 Clothing c = (Clothing) p;
            	 pstmtC = con.prepareStatement(insertC);
            	 pstmtC.setInt(1, key);
        		 pstmtC.setString(2, c.getSize());
        		 pstmtC.setString(3, c.getColour());
        		 pstmtC.executeUpdate();
             }
             if (p instanceof Equipment) {
            	 PreparedStatement pstmtE = null;
            	 String insertE = "insert into Equipment(productID, type, description)"
                         + "values (?, ?, ?)";
            	 Equipment e = (Equipment) p;
            	 pstmtE = con.prepareStatement(insertE);
            	 pstmtE.setInt(1, key);
        		 pstmtE.setString(2, e.getType());
        		 pstmtE.setString(3, e.getDescription());
        		 pstmtE.executeUpdate();
             }
            // DBConnection.commitTransaction();
             
         } catch (SQLException sqlE) {
             System.out.println("SQL Error, Product not inserted");
             System.out.println(sqlE.getMessage());
            // DBConnection.rollbackTransaction();
         } catch (Exception e) {
             e.getMessage();
             //DBConnection.rollbackTransaction();
         }
    	 
    	 return controlInt;
     }
     
     /**
      * Updates the Product db
      */
     @Override
     public int updateProduct(Product p) throws SQLException {
    	 //DBConnection.startTransaction();
    	 int controlInt = -1;

    	 String update = "UPDATE Product SET "
    			 +" name = ?, purchasePrice = ?, salesPrice = ?, rentPrice = ?, "
    			 + "countryOfOrigin = ?, minStock = ?, inStock = ?, "
    			 + "supplierID ? , productType = ? "
    	 		 + "WHERE productID = ?";

    	 //System.out.println(update);
    	 try {
    		 PreparedStatement pstmt = null;
    		 pstmt = con.prepareStatement(update);
    		 pstmt.setString(1, p.getName());
    		 pstmt.setInt(2, p.getPurchasePrice());
             pstmt.setInt(3, p.getSalesPrice());
             pstmt.setInt(4, p.getRentPrice());
             pstmt.setString(5, p.getCountryOfOrigin());
             pstmt.setInt(6, p.getMinStock());
             pstmt.setInt(7, p.getInStock());
             pstmt.setInt(8, p.getSupplierID());
             pstmt.setString(9, p.getProductType());
             pstmt.setInt(10, p.getProductID());
             controlInt = pstmt.executeUpdate();
             
             if (p instanceof GunReplicas) {
            	 PreparedStatement pstmtG = null;
            	 String insertG = "UPDATE GunReplicas SET "
            	 		+ "fabric = ?, calibre = ? "
                         + "WHERE productID = ?";
            	 GunReplicas g = (GunReplicas) p;
            	 pstmtG = con.prepareStatement(insertG);
        		 pstmtG.setString(1, g.getFabric());
        		 pstmtG.setString(2, g.getCalibre());
        		 pstmtG.setInt(3, g.getProductID());
        		 pstmtG.executeUpdate();
             }
             if (p instanceof Clothing) {
            	 PreparedStatement pstmtC = null;
            	 String insertC = "UPDATE Clothing SET "
            	 		+ "size = ?, color = ?"
                         + "WHERE productID = ?";
            	 Clothing c = (Clothing) p;
            	 pstmtC = con.prepareStatement(insertC);
        		 pstmtC.setString(1, c.getSize());
        		 pstmtC.setString(2, c.getColour());
        		 pstmtC.setInt(3, c.getProductID());
        		 pstmtC.executeUpdate();
             }
             if (p instanceof Equipment) {
            	 PreparedStatement pstmtE = null;
            	 String insertE = "UPDATE Equipment SET "
            	 		+ "type = ?, description = ? "
            	 		+ "WHERE productID = ?";
            	 Equipment e = (Equipment) p;
            	 pstmtE = con.prepareStatement(insertE);
        		 pstmtE.setString(1, e.getType());
        		 pstmtE.setString(2, e.getDescription());
        		 pstmtE.setInt(3, e.getProductID());
        		 pstmtE.executeUpdate();
             }
    		 
    		 //DBConnection.commitTransaction();
    	 } catch (SQLException sqlE) {
    		 System.out.println("SQL Error, Product not updated");
    		 System.out.println(sqlE.getMessage());
    		 //DBConnection.rollbackTransaction();
    	 } catch (Exception e) {
    		 e.getMessage();
    		// DBConnection.rollbackTransaction();
    	 }
    	 
    	 return controlInt;
     }
     
     /**
      * Delete a Product from the database
      */
     public int deleteProduct(Product c) throws SQLException 
     {
    	 PreparedStatement pstmt = null;
    	 int controlInt = -1;
    	 
    	 String delete = "DELETE FROM Product WHERE productID = ?";
    	 //System.out.println(delete);
    	 try {
    		 pstmt = con.prepareStatement(delete);
             pstmt.setInt(1, c.getProductID());
             controlInt = pstmt.executeUpdate();
         } catch (SQLException sqlE) {
             System.out.println("SQL Error, Product not deleted");
             System.out.println(sqlE.getMessage());
         } catch (Exception e) {
             e.getMessage();
         }
    	 
    	 return controlInt;
     }
     
     
     /**
 	 * If more than one Product is to be selected
 	 * @param wClause
 	 * @return ArrayList with Product objects
 	 */
     private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
 	 {
    	 ResultSet results;
 	     ArrayList<Product> list = new ArrayList<Product>();	
 		
 	     String query = buildQuery(wClause);
    
 	     try{ // read the cities from the database
 			 Statement stmt = con.createStatement();
 		 	 stmt.setQueryTimeout(5);
 		 	 results = stmt.executeQuery(query);
 		 	
 			 while( results.next() ){ // loop through all cities and create them as objects
 			     Product pObj = new Product();
 				 pObj = buildProduct(results);	
 		         list.add(pObj);	
 			 }//end while
 			
 	         stmt.close();
 	         
 	        if(retrieveAssociation) {
 	        	for(Product pObj : list) {
					IFDBSupplier dbSupplier = new DBSupplier();
					int supplierID = pObj.getSupplierID();
					Supplier supplierObj = dbSupplier.selectSingleSupplier(supplierID);
					if(supplierObj != null) {
					pObj.setSupplier(supplierObj); //set the supplier object in the product object
					}
 	        	}//end for
			}//end if
 		 }//end try
 	 	 catch(Exception e){
 	 	 	System.out.println("Query exception - select: "+e);
 		 	e.printStackTrace();
 	 	 }
 		 return list;
 	 }
     
     /**
 	 * If only one city is to be selected
 	 * @param wClause
 	 * @return Product object
 	 */
 	 private Product singleWhere(String wClause, boolean retrieveAssociation)
 	 {
 	 	ResultSet results;
 	 	Product pObj = new Product();
 		        
 	 	String query = buildQuery(wClause);
 		//System.out.println(query);
 		
 		try{ 	
 			Statement stmt = con.createStatement();
 			stmt.setQueryTimeout(5);
 			results = stmt.executeQuery(query);
 			if( results.next() ){
 				pObj = buildProduct(results);
 				stmt.close();
 				
 				if(retrieveAssociation) {
 					IFDBSupplier dbSupplier = new DBSupplier();
					int supplierID = pObj.getSupplierID();
					
					Supplier supplierObj = dbSupplier.selectSingleSupplier(supplierID);
					if(supplierObj != null) {
					pObj.setSupplier(supplierObj); //set the supplier object in the product object
					}
 				}
 				
 			}else{ //No product found
 				pObj = null;
 			}
 		}//end try	
 		
 		catch(Exception e){
 			System.out.println("Query exception: "+e);
 		}
 		return pObj;
 	 }
 	 
 	 /**
 	 * Method to build the query
 	 * @param wClause
 	 * @return
 	 */
 	 private String buildQuery(String wClause)
 	 {
 	     String query="SELECT * FROM Product";
 		
 	 	if (wClause.length()>0)
 	 		query=query+" WHERE "+ wClause;
 	 		
 	 	return query;
 	 }
     
     /**
      * Builds a Product object from the data in the database
      * @param result
      * @return Product object
      * @throws SQLException
      */
     private Product buildProduct(ResultSet result) throws SQLException {
    	 Product product = new Product();
    	 try {
    		 product.setProductID(result.getInt("productID"));
             product.setName(result.getString("name"));
             product.setPurchasePrice(result.getInt("purchasePrice"));
             product.setSalesPrice(result.getInt("salesPrice"));
             product.setRentPrice(result.getInt("rentPrice"));
             product.setCountryOfOrigin(result.getString("countryOfOrigin"));
             product.setMinStock(result.getInt("minStock"));
             product.setInStock(result.getInt("inStock"));
             product.setSupplier(dbSup.selectSingleSupplier(result.getInt("supplierID")));
             product.getSupplier().setSupplierID(result.getInt("supplierID"));
             product.setSupplierID(result.getInt("supplierID"));
             product.setProductType(result.getString("productType"));
    	 } catch (Exception e) {
    		 System.out.println(e);
         }
         return product;
     }
     
//     private Product buildProduct(ResultSet result) throws SQLException {
//    	 Product product = null;
//    	 try {
//    		 String productType = result.getString("productType");
//    		 productType.toLowerCase();
//    		 if(productType.equals("Clothing".toLowerCase())) {
//    			 product = buildClothingObj(result);
//    		 } else if(productType.equals("GunReplicas".toLowerCase())) {
//    			 product = buildGunReplicaObj(result);
//    		 } else if(productType.equals("Equipment".toLowerCase())) {
//    			 product = buildEquipmentObj(result);
//    		 }
//    	 } catch (Exception e) {
//    		 System.out.println(e);
//         }
//         return product;
//     }
     
//     private Clothing buildClothingObj(ResultSet result) throws SQLException {
//         Clothing clothing = new Clothing();
//         try {
//        	 clothing.setProductID(result.getInt("productID"));
//             clothing.setName(result.getString("name"));
//             clothing.setPurchasePrice(result.getInt("purchasePrice"));
//             clothing.setSalesPrice(result.getInt("salesPrice"));
//             clothing.setRentPrice(result.getInt("rentPrice"));
//             clothing.setCountryOfOrigin(result.getString("countryOfOrigin"));
//             clothing.setMinStock(result.getInt("minStock"));
//             clothing.setInStock(result.getInt("inStock"));
//             clothing.setSupplier(dbSup.selectSingleSupplier(result.getInt("supplierID")));
//             clothing.getSupplier().setSupplierID(result.getInt("supplierID"));
//             clothing.setSupplierID(result.getInt("supplierID"));
//             clothing.setProductType(result.getString("productType"));
//             
//             String query="SELECT * FROM Clothing WHERE productID=?";
//             PreparedStatement ps = con.prepareStatement(query);
//             ps.setInt(1, clothing.getProductID());
//             ResultSet sub = ps.executeQuery();
//             if (sub.next()) {
//            	 clothing.setSize(sub.getString("size"));
//            	 clothing.setColour(sub.getString("color"));
//             }
//         } catch (SQLException e) {
//                 throw e;
//         }
//
//         return clothing;
//     }
//     
//     private GunReplicas buildGunReplicaObj(ResultSet result) throws SQLException {
//    	 GunReplicas gunRep = new GunReplicas();
//    	 try {
//    		 gunRep.setProductID(result.getInt("productID"));
//    		 gunRep.setName(result.getString("name"));
//    		 gunRep.setPurchasePrice(result.getInt("purchasePrice"));
//    		 gunRep.setSalesPrice(result.getInt("salesPrice"));
//    		 gunRep.setRentPrice(result.getInt("rentPrice"));
//    		 gunRep.setCountryOfOrigin(result.getString("countryOfOrigin"));
//    		 gunRep.setMinStock(result.getInt("minStock"));
//    		 gunRep.setInStock(result.getInt("inStock"));
//    		 gunRep.setSupplier(dbSup.selectSingleSupplier(result.getInt("supplierID")));
//    		 gunRep.getSupplier().setSupplierID(result.getInt("supplierID"));
//    		 gunRep.setSupplierID(result.getInt("supplierID"));
//    		 gunRep.setProductType(result.getString("productType"));
//                 
//    		 String query="SELECT * FROM GunReplicas WHERE productID=?";
//    		 PreparedStatement ps = con.prepareStatement(query);
//    		 ps.setInt(1, gunRep.getProductID());
//    		 ResultSet sub = ps.executeQuery();
//    		 if (sub.next()) {
//    			 gunRep.setFabric(sub.getString("fabric"));
//    			 gunRep.setCalibre(sub.getString("calibre"));
//    		 }
//                 
//    	 } catch (SQLException e) {
//    		 throw e;
//    	 }
//     	return gunRep;
//     }
//
//     private Equipment buildEquipmentObj(ResultSet result) throws SQLException {
//         Equipment equipment = new Equipment();
//         try {
//        	 equipment.setProductID(result.getInt("productID"));
//    		 equipment.setName(result.getString("name"));
//    		 equipment.setPurchasePrice(result.getInt("purchasePrice"));
//    		 equipment.setSalesPrice(result.getInt("salesPrice"));
//    		 equipment.setRentPrice(result.getInt("rentPrice"));
//    		 equipment.setCountryOfOrigin(result.getString("countryOfOrigin"));
//    		 equipment.setMinStock(result.getInt("minStock"));
//    		 equipment.setInStock(result.getInt("inStock"));
//    		 equipment.setSupplier(dbSup.selectSingleSupplier(result.getInt("supplierID")));
//    		 equipment.getSupplier().setSupplierID(result.getInt("supplierID"));
//    		 equipment.setSupplierID(result.getInt("supplierID"));
//    		 equipment.setProductType(result.getString("productType"));
//                 
//    		 String query="SELECT * FROM Equipment WHERE productID=?";
//    		 PreparedStatement ps = con.prepareStatement(query);
//    		 ps.setInt(1, equipment.getProductID());
//    		 ResultSet sub = ps.executeQuery();
//    		 if (sub.next()) {
//    			 equipment.setType(sub.getString("type"));
//    			 equipment.setDescription(sub.getString("description"));
//    		 }
//         } catch (SQLException e) {
//                 throw e;
//         }
//         return equipment;
//     }

}
